﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using BCCommon.OAEnums;
using BCCommon.Extensions;
using BCEntity.OA.Attendance.AttendanceClass;
using BCEntity.OA.Attendance.AttendanceClassSetting;
using BCCommon;

namespace BCData.OA.Attendance.AttendanceClass
{
    /// <summary>
    /// 考勤班次
    /// </summary>
    public class AttendanceClassData : IAttendanceClassData
    {
        private const string QUERY = @"SELECT 
	                                    ClassId,
	                                    ClassName,
	                                    WeekDay,RestTime,TotalWorkHours,TotalDays,
	                                    AdminId,
	                                    AdminName,
	                                    CreateTime,ModifierId,
	                                    ModifierName,
	                                    ModifyTime,
	                                    Status,
                                        RestTime
                                    FROM
	                                    OA_AttendanceClass Where Status<>'Abandon'";

        private IDatabaseContext databaseContext;

        public AttendanceClassData(IDatabaseContext databaseContext)
        {
            this.databaseContext=databaseContext;
        }

        #region 删除操作

        /// <summary>
        /// 删除指定的班次信息
        /// </summary>
        /// <param name="classId">班次Id</param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(long classId, string adminName = null)
        {
            string sql = GetDeleteAttendanceClassSql(classId, adminName, out DynamicParameters parameters);
            
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction);
        }


        private string GetDeleteAttendanceClassSql(long classId, string adminName, out DynamicParameters parameters)
        {
            parameters = new DynamicParameters();
            string sql = SqlCommands.GetUpdateSql("OA_AttendanceClass", new string[] { "Status", "ModifierName", "ModifyTime" }, new string[] { "ClassId" });
            parameters.Add("?ClassId", classId, DbType.Int64);
            parameters.Add("?Status", "Abandon", DbType.AnsiString, size: 50);
            parameters.Add("?ModifierName", adminName, DbType.AnsiString, size: 50);
            parameters.Add("?ModifyTime", DateTime.Now, DbType.DateTime);
            return sql;
        }

        #endregion

        #region 查询操作

        /// <summary>
        ///分页查看考勤班次相关数据
        /// </summary>
        /// <param name="pageIndex">起始页</param>
        /// <param name="pageSize">页数量</param>
        /// <param name="ascending">排序</param>
        /// <returns>字典数据列表</returns>
        public async Task<(IEnumerable<AttendanceClassEntity>, int)> GetAsync(int pageIndex, int pageSize, bool ascending, string className)
        {
            string sql = QUERY;

            DynamicParameters parameters = new DynamicParameters();
            var wheres = new List<string>();
            if (className.IsNotNull())
            {
                wheres.Add("ClassName=?ClassName");
                parameters.Add("?ClassName", className.Trim(), DbType.AnsiString, size: 50);
            }

            parameters.Add("?pageIndex", (pageIndex - 1) * pageSize, DbType.Int32);
            parameters.Add("?pageSize", pageSize, DbType.Int32);

            sql = string.Format("{0} {2} ORDER BY ClassName {1} LIMIT ?pageIndex,?pageSize; SELECT COUNT(1) FROM OA_AttendanceClass {2} Where Status<>'Abandon';",
                sql,
                ascending ? "ASC" : "DESC",
                wheres.Count > 0 ? string.Join(" AND", wheres) : "");

            return await this.databaseContext.Slave.QueryMultipleAsync<AttendanceClassEntity,int>(sql, parameters);
        }

        public async Task<AttendanceClassEntity> GetEntityAsync(long classId)
        {
            string sql = SqlCommands.GetQuerySql("OA_AttendanceClass", new[] { "ClassId", "ClassName", "WeekDay", "RestTime", "AdminId", "AdminName", "CreateTime",
                "ModifierName", "ModifyTime", "Status" }, new[] { "ClassId" }, "");

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ClassId", classId, DbType.Int64);

            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<AttendanceClassEntity>(sql, parameters);
        }


        public async Task<IEnumerable<AttendanceClassViewEntity>> GetClassViewAsync(long classId)
        {
            string sql = @"SELECT B.ClassId,B.ClassName,B.TotalDays,B.TotalWorkHours,A.Start,A.End 
                            FROM oa_attendanceclasssetting A  
                            LEFT JOIN oa_attendanceclass B ON A.ClassId = B.ClassId  
                            WHERE B.Status=?Status ";
            DynamicParameters parameters = new DynamicParameters();
            if (classId > 0)
            {
                sql += " AND ClassId=?ClassId ";
                parameters.Add("?ClassId", classId, DbType.Int64);
            }
            parameters.Add("?Status", AttendanceBaseStatus.NewCreate.ToString(), DbType.AnsiString, size: 50);
            return await this.databaseContext.Slave.QueryAsync<AttendanceClassViewEntity>(sql, parameters);
        }

        /// <summary>
        /// 校验班次信息存在不
        /// </summary>
        /// <param name="classId">班次Id</param>
        /// <returns></returns>
        public async Task<bool> CheckIsExist(long classId)
        {
            string sql = @"SELECT 1 FROM OA_AttendanceClass WHERE ClassId=?ClassId AND  Status<>'" + AttendanceBaseStatus.Abandon.ToString() + "' LIMIT 1";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ClassId", classId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameter) != null;
        }
        #endregion

        #region 新增操作

        public async Task<AttendanceClassEntity> InsertAsync(AttendanceClassEntity attendanceClass, List<AttendanceClassSettingEntity> classSettingEntities)
        {
            List<string> sqls = new List<string>();
            List<DynamicParameters> parameters = new List<DynamicParameters>();
            string sql = @"INSERT INTO OA_AttendanceClass(ClassName,WeekDay,RestTime,TotalWorkHours,TotalDays,AdminId,AdminName)VALUES(?ClassName,?WeekDay,?RestTime,?TotalWorkHours,?TotalDays,?AdminId,?AdminName);
                            SELECT LAST_INSERT_ID();";
            DynamicParameters parameters1 = new DynamicParameters();
            parameters1.Add("?ClassName", attendanceClass.ClassName, DbType.AnsiString, size: 50);
            parameters1.Add("?WeekDay", attendanceClass.WeekDay, DbType.AnsiString, size: 50);
            parameters1.Add("?RestTime", attendanceClass.RestTime, DbType.AnsiString, size: 50);
            parameters1.Add("?TotalWorkHours", attendanceClass.TotalWorkHours, DbType.AnsiString, size: 30);
            parameters1.Add("?TotalDays", attendanceClass.TotalDays, DbType.Decimal, size: 10);
            parameters1.Add("?AdminId", attendanceClass.AdminId, DbType.Int64);
            parameters1.Add("?AdminName", attendanceClass.AdminName, DbType.AnsiString, size: 30);
            long classId = this.databaseContext.Master.ExecuteScalarAsync<long>(sql, parameters1, databaseContext.Transaction).Result;
            if (classId > 0)
            {
                foreach (var item in classSettingEntities)
                {
                    item.ClassId = classId;
                    sql = GetInsertClassSettingSql(item, out DynamicParameters parameter);
                    var result = await this.databaseContext.Master.ExecuteAsync(sql, parameter, databaseContext.Transaction) >0;
                    if (!result)
                    {
                        throw new ArgumentException("添加考勤班次设置失败!");
                    }
                }
            }    
            string querySql = string.Format(@"{0} AND ClassId=" + classId, QUERY);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<AttendanceClassEntity>(querySql);
            
        }

        private string GetInsertClassSettingSql(AttendanceClassSettingEntity entity, out DynamicParameters parameters)
        {
            parameters = new DynamicParameters();
            string sql = SqlCommands.GetInsertSql("OA_AttendanceClassSetting", new string[] { "ClassId", "Start", "End" });
            parameters.Add("?ClassId", entity.ClassId, DbType.Int64, size: 20);
            parameters.Add("?Start", entity.Start, DbType.AnsiString, size: 50);
            parameters.Add("?End", entity.End, DbType.AnsiString, size: 50);
            return sql;
        }
        #endregion

        #region 修改操作
        /// <summary>
        /// 修改考勤班次
        /// </summary>
        /// <param name="attendanceClass"></param>
        /// <param name="classSettingEntities"></param>
        /// <returns></returns>
        public async Task<AttendanceClassEntity> UpdateAsync(AttendanceClassEntity attendanceClass, List<AttendanceClassSettingEntity> classSettingEntities)
        {
            
            string sql = GetUpdateAttendanceClassSql(attendanceClass, out DynamicParameters parameters);
            this.databaseContext.Master.Execute(sql, parameters, databaseContext.Transaction);

            sql = this.GetDeleteClassSettingSql(attendanceClass.ClassId, out DynamicParameters parametersTwo);
            this.databaseContext.Master.Execute(sql, parametersTwo, databaseContext.Transaction);

            if (classSettingEntities.Any())
            {
                foreach (var item in classSettingEntities)
                {
                    item.ClassId = attendanceClass.ClassId;
                    sql =  GetInsertClassSettingSql(item, out DynamicParameters parameter);
                    this.databaseContext.Master.Execute(sql, parameter, databaseContext.Transaction);
                }
            }

            string querySql = string.Format(@"{0} AND ClassId=?ClassId;", QUERY);
            DynamicParameters queryParameter = new DynamicParameters();
            queryParameter.Add("?ClassId", attendanceClass.ClassId, DbType.Int64, size: 20);

            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<AttendanceClassEntity>(querySql, queryParameter);
        }

        private string GetUpdateAttendanceClassSql(AttendanceClassEntity entity, out DynamicParameters parameters)
        {
            parameters = new DynamicParameters();
            string sql = SqlCommands.GetUpdateSql("OA_AttendanceClass", new string[] { "ClassName", "WeekDay", "RestTime", "TotalWorkHours", "TotalDays", "ModifierName", "ModifyTime", "ModifierId" }, new string[] { "ClassId" });
            parameters.Add("?ClassId", entity.ClassId, DbType.Int64, size: 20);
            parameters.Add("?ClassName", entity.ClassName, DbType.AnsiString, size: 50);
            parameters.Add("?WeekDay", entity.WeekDay, DbType.AnsiString, size: 50);
            parameters.Add("?RestTime", entity.RestTime, DbType.AnsiString, size: 50);
            parameters.Add("?TotalWorkHours", entity.TotalWorkHours, DbType.AnsiString, size: 30);
            parameters.Add("?TotalDays", entity.TotalDays, DbType.Decimal, size: 10);
            parameters.Add("?TotalWorkHours", entity.TotalWorkHours, DbType.AnsiString, size: 30);
            parameters.Add("?TotalDays", entity.TotalDays, DbType.Decimal, size: 10);
            parameters.Add("?ModifierId", entity.ModifierId, DbType.Int64, size: 20);
            parameters.Add("?ModifierName", entity.AdminName, DbType.AnsiString, size: 30);
            parameters.Add("?ModifyTime", DateTime.Now, DbType.DateTime);
            return sql;
        }

        private string GetDeleteClassSettingSql(long classId, out DynamicParameters parameters)
        {
            parameters = new DynamicParameters();
            string sql = "DELETE FROM OA_AttendanceClassSetting WHERE ClassId=?ClassId ";
            parameters.Add("?ClassId", classId, DbType.Int64);
            return sql;
        }

        /// <summary>
        /// 获取考勤班次以及班次设置信息
        /// </summary>
        /// <param name="classId">班次Id</param>
        /// <returns></returns>
        public async Task<IEnumerable<AttendanceClassViewEntity>> GetAttendanceClassWithSettings(long classId)
        {
            string sql = @"SELECT A.ClassId,A.ClassName,B.Start,B.End 
                            FROM OA_AttendanceClass A 
                            INNER JOIN OA_AttendanceClassSetting B ON A.ClassId=B.ClassId  
                            WHERE A.ClassId=?ClassId  
                            AND A.Status<>'Abandon'";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ClassId", classId, DbType.Int64);
            return await this.databaseContext.Slave.QueryAsync<AttendanceClassViewEntity>(sql, parameters);
        }

        #endregion
    }
}
